<?php
/**
 * 数据库类，mysqli封装
 * */

class mydb {
	public $db ;
	public $sql;
	public $logUpdateSql = true; //默認寫更新日誌
	public $dieError = true; //默認遇到錯誤就終止
	public $insertNum = 0;
	public $updateNum = 0;
	public $operateId = 0;
	
	public function  __construct($isDebug=false){
		$this->db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME,DB_PORT);
		$this->db->set_charset(DB_CHARSET);
	}

	public function getFullColumns($tableName){
		$this->sql = 'SHOW FULL COLUMNS FROM    `'.$tableName.'` ';
		return $this->db->query($this->sql);
	}

	public function getFieldComment($tableName,$field){
		$fieldsComment = $this->getFullColumns($tableName);
		foreach ($fieldsComment as $oneFieldInfo){
			if($oneFieldInfo['Field'] == $field){
				return $oneFieldInfo['Comment']==''?$oneFieldInfo['Field']:$oneFieldInfo['Comment'];
			}
		}
	}

	/**
	 * 兼容以前的代码
	 * */
	public function Execute($sql){
		return $this->db->query($sql);
	}


	/**
	 * 兼容以前的代码
	 * */
	public function Affected_Rows(){
		return $this->db->affected_rows;
	}

	/**
	 * 兼容以前的代码
	 * */
	public function affectedRows(){
		return $this->db->affected_rows;
	}


	/**
	 * 将当前的sql语句记录到日志文件
	 * */
	public function logSql(){
		$log = $this->sql.";\r\n";
		require_once(LIB_PATH.'/log/mylog.php');
		$sqlFile = dirname(ROOT_PATH).'/sql/sql'.date('Ymd').'.php';
		mylog::write($log,$sqlFile);
	}


	/**
	 * @param String $sql
	 * @return Array $result
	 * 查询并返回结果集
	 * */

	private function showError(){
		global $router;
		$objName = $router->getControllerName();
		$actName = $router->getActName();

		$log = date("Y-m-d H:i:s ")." $objName,$actName  ".$this->db->error.' : '.$this->sql."\r\n";
		require_once(LIB_PATH.'/log/mylog.php');
		mylog::write($log);

		if(IS_DEBUG){
			echo ('error excute:'.$this->sql."<br />\r\n") ;
			if(!IS_LINUX) echo $this->db->error."<br>\r\n";
			myresponse::showConfirm("发生了某种错误，请通知管理员查看系统日志");
		}else{
			myresponse::showConfirm("发生了某种错误，请通知管理员查看系统日志");
		}

	}

	public function query( $sql){
		$this->sql = $sql;

		$sqlType = strtolower(substr(trim($this->sql),0,4));
		$result = $this->db->query($this->sql);

		if($result===false && $this->dieError){
			$this->showError();
		}

		if(($sqlType == 'sele') || ($sqlType == 'show')){//
			/* fetch associative array */
			$allRows = [];
			while ($row = $result->fetch_assoc()) {
				$allRows[] = $row;
			}
			/* free result set */
			$result->free();
			return $allRows;
		}
		return $result;
	}

	/**
	 * @param String $sql
	 * @return Array $row (目标只有一行数据，返回一行结果)
	 * 查询并返回结果集
	 * */
	public function getOne($sql){
		$this->sql = $sql;
		$result = $this->db->query($this->sql);
		if($this->db->errno && $this->dieError){
			$this->showError();
		}
		if(!$result) return false;
		if($result===true && IS_DEBUG){
			echo $this->sql;
			var_dump($result);
			die();
		}
		
		$row = $result->fetch_assoc();
		$result->free();
		return $row;
	}


	/**
	 * @param String $this->sql
	 * @return Array $result
	 * 查询并返回结果集
	 * */
	public function getRow( $sql ){
		return $this->getOne($sql);
	}

	/**
	 * @param String $sql
	 * @return Array $result
	 * 查询并返回结果集
	 * */
	public function getAll( $sql ){
		return $this->query($this->sql);
	}

	/**
	 * @param String $tableName 表
	 * @param Array $record 记录
	 * 向数据库插入一条记录
	 * @return Bool
	 * */
	public function insert( $tableName , $record ){
		if(!is_array($record)){
			die('error $record for $db->insert("'.$tableName.'",$record)');
		}
		$this->tableName = $tableName;

		$fields = '';
		$values = '';
		foreach ($record as $key => $val){
			$fields .= ', `'.$key.'` ';
			if(strtolower($val) =='null' or $val===null ){
				$values .= ', NULL ';
			}else{
				$values .= ', "'.addslashes($val).'" ';
			}
		}

		$fields = substr($fields,1);
		$values = substr($values,1);

		$this->sql    = 'INSERT INTO '.$tableName.' ( '.$fields.' ) VALUES ( '.$values.' )';
		return $this->query($this->sql);
	}

	/**
	 * 查询表中是否存在对应的数据，如果不存在，插入，如果存在，更新
	 * 
	 * */
	public function insertOrUpdate( $tableName , $record ,$where,$allowMultiUpdate=false){
		if($where == ''){
			die('error:$where needed!');
		}

		$this->sql = "SELECT COUNT(1) as FNum FROM $tableName WHERE $where";
		$result = $this->query($this->sql);
		if($result[0]['FNum'] == 0){
			$this->insert($tableName,$record);
		}else if($result[0]['FNum'] == 1){
			$this->update($tableName,$record,$where);
		}else if($allowMultiUpdate) {//不支持一个以上的符合条件的记录的更新操作
			$this->update($tableName,$record,$where);
		}else{
            die("error \$where,too many records:$where !");
        }

		if($this->affectedRows()>0){
			return true;
		}else {
			return false;
		}

	}

	/**
	 * 查询表中是否存在对应主键的数据，如果不存在，插入，如果存在，更新
	 * 
	 * */
	public function updateOrInsert( $tableName , $record ,$where){
		return $this->insertOrUpdate($tableName , $record ,$where);
	}

	/**
	 * 返回当前执行的sql
	 * */
	public function getSql(){
		return $this->sql;
	}



	/**
	 * @param String $tableName 表
	 * @param Array $record 记录
	 * 更新一条记录
	 * @return Bool
	 * */
	public function update( $tableName , $record , $where ){
		if(trim($where) == ''){
			die("error: \$db->update() need a not null \$where param");
		}
		$this->tableName = $tableName;

		$where = $this->getWhere($where);
		$this->sql = '';
		foreach ($record as $key => $val){

			if(strtolower($val) =='null'  or $val===null){
				$this->sql .= ', `'.$key.'`=NULL ';
			}if(substr($val,0,1) =='`'){
				$this->sql .= ', `'.$key.'`='.$val;
			}else{
				$this->sql .= ', `'.$key.'`="'.addslashes($val).'" ';
			}
		}
		$this->sql = substr($this->sql,1);

		$this->sql = 'UPDATE '.$tableName.' SET '.$this->sql.$where;
		return $this->query($this->sql);
	}

	/**
	 * 
	 * 获取更新的行数
	 * @return Int $num
	 * */
	public function getAffectedRows(){
		return $this->db->affected_rows;
	}

	/**
	 * 获取插入的行号
	 *  @return Int $num
	 * */
	public function getInsertId(){
		return $this->db->insert_id;
	}

	/**
	* 获取数据库的缓存表中的数据或者执行sql查询
	* @param String  $sql $this->sql需要执行的sql
	* @param String $enName 记录缓存的英文命名 $enName
	* @param String $reason 记录缓存的中文说明 $reason
	* @param Int $lifeTime 记录缓存的有效时长 $lifeTime
	 * @return Array
	*
	* */
	public function cacheQuery($sql,$enName='',$reason='',$lifeTime=60){
		if($lifeTime<=0 || (defined('USE_DB_CACHE') && !USE_DB_CACHE)){
			return $this->query($sql);
		}
		$result = $this->getCache($sql,$lifeTime,$enName);
		if(count($result) > 0){//已经有缓存,直接返回缓存数组内容
			return $result;
		}else{//将sql语句的查询结果存入缓存并返回
			$result = $this->query($sql);

			$this->writeCache($sql,$result,$enName,$reason,$lifeTime);
			return $result;
		}
		$this->sql = $sql; //cacheQuery结束后更新$this->sql
	}

	/**
	 * 获取sql对应的key的算法
	 * */
	public function getKey($sql,$enName=''){
		if($enName) return $enName;
		return md5($sql);
	}


	/**
	* 获取数据库中的缓存的数组内容
	* */
	public function getCache($sql, $lifeTime=60,$enName=''){
		$this->sql = $sql;
		$sqlKey = $this->getKey($this->sql,$enName);
		$this->sql = 'SELECT FCacheId,FText,FUpdateTime FROM `tbl_my_cache` WHERE FSqlKey="'.$sqlKey.'"';
		$result = $this->query($this->sql);
		if($result){//已有缓存
			if((time()-strtotime($result[0]['FUpdateTime'])) > $lifeTime){//缓存过期了
				$this->sql = 'UPDATE `tbl_my_cache` SET FMissTimes=FMissTimes+1 WHERE FCacheId="'.$result[0]['FCacheId'].'"';
				$this->query($this->sql,'tbl_my_cache');
				return array();
			}else {//缓存未过期
				$this->sql = 'UPDATE `tbl_my_cache` SET FHitTimes=FHitTimes+1 WHERE FCacheId="'.$result[0]['FCacheId'].'"';
				$this->query($this->sql,'tbl_my_cache');
				return unserialize($result[0]['FText']);
			}
		}else {
			return array();
		}
	}//end funciton

	/**
	* 将数组写入数据库缓存
	* */
	public function writeCache($srcSql,$cacheArray,$enName='',$reason='',$lifeTime=60){
		$this->sql = $srcSql;
		$cacheStr = serialize($cacheArray);//序列化结果对象
		$sqlKey = $this->getKey($this->sql,$enName);
		//FHitTimes更新是为了保证当前的数据一定有变化，避免重复插入，其实这个和下边的insert语句应该是一个事务
		$updateSql = 'UPDATE `tbl_my_cache` SET FHitTimes=FHitTimes+1,FText="'.addslashes($cacheStr).'",FUpdateTime="'.date('Y-m-d H:i:s').'",FLifeTime='.intval($lifeTime).' WHERE FSqlKey="'.$sqlKey.'"';
		$result = $this->query($updateSql,'tbl_my_cache');

		if($this->getAffectedRows()<1){//刷新太快也不会有这个,并发太高的时候呢，没有插入数据@todo 这里有BUG
			$insertSql = 'INSERT INTO `tbl_my_cache` (FSqlKey,FSql,FEnName, FTime,FUpdateTime,FReason,FText,FLifeTime) VALUES ("'.$sqlKey.'","'.addslashes($srcSql).'","'.addslashes($enName).'","'.date('Y-m-d H:i:s').'","'.date('Y-m-d H:i:s').'","'.addslashes($reason).'","'.addslashes($cacheStr).'",'.intval($lifeTime).')';
			$this->query($insertSql,'tbl_my_cache');
		}
	}

	/**
	 * 获取一个数据表中存在的数据
	 * */
	public function getCount($table,$where=''){
		$where = $this->getWhere($where);
		$this->sql = 'SELECT COUNT(1) AS Num FROM `'.$table.'` '.$where;
		$result = $this->query($this->sql);
		return $result[0]['Num'];
	}

	/**
	 * 获取where条件
	 * "FEmail=xuleit@126.com" => FEmail = "xuleit@126.com" @todo
	 * 
	 * */
	public function getWhere($where){

		if($where != '' && stristr($where,'where')===false){
			$where = ' WHERE '.$where;
		}
		return $where;
	}

	public function close(){
		return $this->db->close();
	}
}